<?php
/**
 * @package model.dao
 * @author Sanu S <sanu@synergyquad.com>
 * @author Modified By Sanu S
 * @copyright Copyright &copy; 2011, synergyquad
 * @since 1.0 - May-18-2011
 * @version 1.1
 */

require_once 'vo/UserVO.php';
require_once 'vo/AdminVO.php';
require_once 'vo/ForumVO.php';
require_once 'vo/CompanyVO.php';
require_once 'vo/AttachmentVO.php';
require_once 'vo/NotificationVO.php';


class AdminDAOImpl
{
	
	/**
	 * @access public
	 * @staticvar Instance of MemberDAOImpl
	 */
	public static $_instance	= null;
	
	/**
	 * @access public
	 */
	var $_mdb2				= null;
	
	/**
	 * <p>Get an instance of MemberDAOImpl</p>
	 * 
	 * @access public
	 * @return MemberDAOImpl Instance of MemberDAOImpl
	 */
	public static function getInstance() 
	{
		if(self::$_instance == null) 
		{
			self::$_instance = new AdminDAOImpl();
		}
		
		return self::$_instance;
	}
		
	/**
	 * <p>To get the user start up data by the given user id</p>
	 * 
	 * @access public
	 * @global Array Configuration Array
	 * @param Integer $userId The id of the User	 
	 * @return $userStartUp the UserSettingsVO  and MemberRegVO
	 */
	public function loginUser($request) 
	{
		$userName =  $request->username;
		$password = $request->password;
          
               
        //$query_data = "SELECT administrator.*, company.company_name FROM administrator LEFT JOIN company ON administrator.company=company.id WHERE administrator.email = '".$userName."' AND administrator.password = '".$password."'";
         $query_data = "SELECT user_master.*, company.company_name, user_roles.role_id, role_master.role_name FROM user_master LEFT JOIN company ON user_master.company_id=company.id LEFT JOIN user_roles ON user_master.user_id=user_roles.user_id LEFT JOIN role_master ON user_roles.role_id=role_master.role_id WHERE user_master.email = '".$userName."' AND user_master.password = '".$password."'";
       //$query_data = "SELECT * FROM administrator WHERE email = '".$userName."' AND password = '".$password."'";        
       
        $result_data = mysql_query($query_data) or die("Query ERROR");
        
		$userVO = new UserVO();
		$row =  mysql_fetch_object($result_data);
	
                
        if($row)
        {
		session_start();
		
		$_SESSION['auth'] = $row->email;
        	$userVO->admin_id= $row->user_id;
        	$userVO->result = true;
        	$userVO->resultString = "User Login success";
        	$userVO->username = $row->email;
        	$userVO->password = null;
                $userVO->company = $row->company_id;
                $userVO->company_name = $row->company_name;
        	$userVO->adminType = $row->role_name;
                $userVO->roleId = $row->role_id;
        	$userVO->firstName = $row->first_name;
        	$userVO->lastName = $row->last_name;
        	$userVO->image_url = $row->image_url;
        	$userVO->image_thump = $row->image_thump;
        	
        }else {
        	
        	$userVO->result = false;
        	$userVO->resultString = "Login Error";
        }
       
        return $userVO;

	}
        
        public function getTowerCompany()
       {
          $query_data = "SELECT * FROM company WHERE company_type='Tower company'" ;
        $result_data = mysql_query($query_data) or die("Query ERROR");
        $arrayCollection = new ArrayCollection();
        $tmps = array();
        $i = 0;
       
        while ($row =  mysql_fetch_object($result_data))
        {
            $tmp = new CompanyVO();
            $tmp->company_id = $row->id;
            $tmp->company_name = $row->company_name;
            $tmp->company_type = $row->company_type;
            $tmp->managed_by = $row->managed_by;
            $tmp->street = $row->street;
            $tmp->city = $row->city;
            $tmp->state = $row->state;
            $tmp->country = $row->country;
            $tmp->zip_code = $row->zip_code;
            $tmp->latitude = $row->latitude;
            $tmp->longitude = $row->longitude;
            $tmp->email = $row->email;
            $tmp->phone = $row->phone;
            $tmp->fax = $row->fax;
            $tmp->active = $row->active;
            $tmp->created_by = $row->created_by;
            $tmp->created_date = $row->created_date;
            $tmp->updated_by = $row->updated_by;
            $tmp->updated_date = $row->updated_date;
           
            $tmps[$i] =    $tmp;
            $i++;
        }
        if(count($tmps) >0)
            $arrayCollection->source = $tmps;
        return $arrayCollection;
      }
      
      public function getPartners($role_id)
      {
          $query_data = "SELECT user_master.*, role_master.role_name, user_roles.role_id FROM user_master LEFT JOIN user_roles ON user_master.user_id=user_roles.user_id LEFT JOIN role_master ON user_roles.role_id=role_master.role_id WHERE role_mater.role_id=".$role_id;
          $result_data = mysql_query($query_data) or die("Query ERROR");
          $arrayCollection = new ArrayCollection();
		$tmps[] = '';
		$i = 0;
		
		while ($row =  mysql_fetch_object($result_data)) 
		{
			$tmps[$i] =	self::createAdminObject($row);
			$i++;
                 }
		$arrayCollection->source = $tmps;
		
		return $arrayCollection;
        }
	 
	public function getAlluser($start,$count, $searchKey)
	{
		$where='';
		$query_data = "SELECT user_master.*, role_master.role_name, user_roles.role_id FROM user_master LEFT JOIN user_roles ON user_master.user_id=user_roles.user_id LEFT JOIN role_master ON user_roles.role_id=role_master.role_id ";
		if($searchKey)
			$where= "WHERE user_master.first_name LIKE '%".$searchKey."%' OR user_master.last_name LIKE '%".$searchKey."%' OR user_master.email LIKE '%".$searchKey."%' ";
		$query_data .=$where."LIMIT ".$start.",".$count;
		
		$query_count = "SELECT COUNT(*) as count FROM user_master ".$where;
		$result_count = mysql_fetch_array(mysql_query($query_count));
		
		  
        $result_data = mysql_query($query_data) or die($query_data);
      
        $arrayCollection = new ArrayCollection();
		$tmps[] = '';
		$i = 0;
		
		while ($row =  mysql_fetch_object($result_data)) 
		{
			$tmps[$i] =	self::createAdminObject($row);
			$i++;
        }
		$arrayCollection->count = $result_count["count"];
		$arrayCollection->source = $tmps;
		
		return $arrayCollection;
	}
	public function getAllAdministrator()
	{
		
		$query_data = "SELECT user_master.*, role_master.role_name, user_roles.role_id FROM user_master LEFT JOIN user_roles ON user_master.user_id=user_roles.user_id LEFT JOIN role_master ON user_roles.role_id=role_master.role_id  ";
        $result_data = mysql_query($query_data) or die($query_data);
        
        $arrayCollection = new ArrayCollection();
		$tmps[] = '';
		$i = 0;
		
		while ($row =  mysql_fetch_object($result_data)) 
		{
			$tmps[$i] =	self::createAdminObject($row);
			$i++;
        }
		$arrayCollection->source = $tmps;
		
		return $arrayCollection;
	}
	
	public function createNewAdministrator($admin)
	{
		$user_name_query = mysql_query("SELECT user_id FROM user_master WHERE email = '".$admin->email."'");
		$u_result = mysql_fetch_array($user_name_query);
		if(!$u_result['user_id']) 
		{
                    $role_result = mysql_query("SELECT role_id FROM role_master WHERE role_name='".$admin->admin_type."'");
                    if(mysql_num_rows($role_result))
                    {
                         $role_row = mysql_fetch_array($role_result);
                         $role_id = $role_row["role_id"];
                    }
                    if(!isset($admin->compnay_id))
                         $admin->compnay_id= 2;  
                    
                   $query_insert = "INSERT INTO user_master ( `user_id` , `email` , `password`, `parent_user_id`, `company_id`, `first_name` , `last_name`) VALUES (NULL,'" .$admin->email. "','" .$admin->password. "', 2, " .$admin->company_id. ", '" .$admin->first_name. "','" .$admin->last_name."')";
                   
	        $result_data = mysql_query($query_insert) or die("Query ERROR103" . $query_insert );
                if($result_data)
                {
                    $user_id = mysql_insert_id();
                    mysql_query("INSERT INTO user_roles (user_id, role_id) VALUES(". $user_id.", ".$role_id." )");
                }    
	        $lastInsertedID = $user_id;
	        return $lastInsertedID;
		}
		else
		{
			$result = "Duplicate";
			return $result;
		} 
		
	}
	
	public function updateAdministratorByID($admin)
	{
		$query_insert = "UPDATE user_master SET email='" .$admin->email. "', password='" .$admin->password. "', first_name='" .$admin->first_name. "', last_name='" .$admin->last_name. "' WHERE user_id=".$admin->admin_id;
		$result_insert = mysql_query($query_insert) or die("Query ERROR104".$query_insert);
                if($result_insert)
                  if(mysql_num_rows(mysql_query("SELECT user_id FROM user_roles WHERE user_id=".$admin->admin_id)))  
                         mysql_query("UPDATE user_roles SET role_id=(SELECT role_id FROM role_master WHERE role_name='".$admin->admin_type."') WHERE user_id =".$admin->admin_id); 
                  else
                      mysql_query("INSERT INTO user_roles (user_id, role_id) VALUES(".$admin->admin_id.", (SELECT role_id FROM role_master WHERE role_name='".$admin->admin_type."'))");
	}
	
	public function deleteAdministratorByID($id)
	{
		$query_insert = "DELETE FROM user_master WHERE user_id =".$id;
		$result_insert = mysql_query($query_insert) or die("Query ERROR105".$query_insert);
                if($result_insert)
                    mysql_query("DELETE FROM user_roles WEHERE user_id=".$id);
	}

	public function addNewLog($vehicle_id, $log_desc, $log_action) 
	{
		$myDate = date('Y-m-d H:i:s');
		$my_insert = "INSERT INTO log_details ( `log_id` , `vehicle_id` , `log_desc` , `log_action` ,date  ) VALUES (NULL,'".$vehicle_id. "','" .$log_desc. "','" .$log_action. "','" .$myDate."')";
		$result_insert = mysql_query($my_insert) or die("Query ERROR");
		return $result_insert;
    }
    
    private function createAdminObject($row)
    {
    	$tmp = new AdminVO();
        $tmp->admin_id = $row->user_id;
		$tmp->admin_type = $row->role_name;
		$tmp->email = $row->email;
		$tmp->first_name = $row->first_name;
		$tmp->last_name = $row->last_name;
		$tmp->password = $row->password;
		$tmp->image_url = $row->image_url;
		$tmp->image_thump = $row->image_thump;
		return $tmp;
    }
    
	public function asignLoginStatus($status)
	{
		$myDate = date('Y-m-d H:i:s');
		$user_check = "SELECT * FROM user_login_status WHERE admin_id =".$status->admin_id;
		$user_result =     mysql_query($user_check) or die("Query ERROR");
		
		if(mysql_fetch_object($user_result))
		{
		    $status_update = "UPDATE  user_login_status SET peer_id='".$status->peer_id."', date='".$myDate."' WHERE admin_id=".$status->admin_id;
		    $result_update = mysql_query($status_update) or die("Query ERROR");
		}
		else
		{
			$status_insert = "INSERT INTO user_login_status ( `admin_id` , `admin_name` ,`type`, `peer_id` , `status` ,`date`  ) VALUES (".$status->admin_id.",'".$status->admin_name. "','".$status->type. "','" .$status->peer_id. "','" .$status->status. "','" .$myDate."')";
			$result_status = mysql_query($status_insert) or die("Query ERROR");  
		}
	
		$list = self::getStatusList();
		return $list;
	}

	public function getStatusList()
	{
	    $status_select = "SELECT * FROM user_login_status";
		$status_insert = mysql_query($status_select) or die("Query ERROR");
	    $arrayCollection = new ArrayCollection();
        $tmps =  array();
        $i = 0;
        
        while ($row =  mysql_fetch_object($status_insert))
        {
            $tmps[$i] =    self::createStatusObject($row);
            $i++;
        }
        
        $arrayCollection->source = $tmps;
      
        return $arrayCollection;
	}

	private function createStatusObject($row)
    {
        $tmp = new StatusVO();
        $tmp->admin_id = $row->admin_id;
        $tmp->admin_name = $row->admin_name;
		$tmp->type = $row->type;
        $tmp->peer_id= $row->peer_id;
        $tmp->status = $row->status;
        $tmp->date = $row->date;
      
        return $tmp;
    }
    
	public function changeStatus($peerID)
    {
        $status_update = "UPDATE  user_login_status SET status='Busy' WHERE peer_id='".$peerID."'";
    	$result_update = mysql_query($status_update) or die("Query ERROR");
        return $result_update;
    }
    
	public function deleteUser($peerID)
    {
        $status_update1 = "DELETE FROM user_login_status WHERE peer_id='".$peerID."'";
    	$result_update1 = mysql_query($status_update1) or die("Query ERROR");
        return $result_update1;
    }
	public function getCommentListByParentID($forum)
	{
	    $parent_ID = 'f.parent_id IS NULL';
	    
	    if($forum->parent_id != 0)
	    {
	    	$parent_ID = 'f.parent_id ='.$forum->parent_id;
	    }
	
	    $myDate = date('Y-m-d H:i:s');
	    $my_insert = "SELECT f.comment_id, f.admin_id, f.parent_id, f.root, f.comment, f.update_date, f.date, f.priority, f.ref_attach, a.first_name, a.image_thump FROM administrator a, admin_forum f WHERE ".$parent_ID." AND a.admin_id = f.admin_id ORDER BY update_date DESC LIMIT ".$forum->start_index.", ".$forum->interval;
	    $result_insert = mysql_query($my_insert) or die("Query ERROR".$my_insert);
	    $arrayCollection = new ArrayCollection();
	    $tmps = array();
	    $i = 0;

	    while ($row =  mysql_fetch_object($result_insert))
	    {
	        $tmps[$i] =    self::createCommentList($row);
	        $i++;
	    }
	    $arrayCollection->source = $tmps;
	    $forumList = new ForumVO();
	    $forumList->reply_array = $arrayCollection;
	    if($forum->parent_id == 0)
	    {
	        $query_count = mysql_query("SELECT count(*) AS total FROM admin_forum WHERE parent_id IS NULL");
	        $result_co = mysql_fetch_array($query_count);
	        $forumList->total_sub = $result_co['total'];
	    }
    	return $forumList;
	}
	
	private function createCommentList($row)
	{   
	    $tmp = new ForumVO();
	    $tmp->comment_id = $row->comment_id;
	    $tmp->parent_id = $row->parent_id;
	    $tmp->admin_id = $row->admin_id;
		$tmp->admin_url = $row->image_thump;
	    $query_n = mysql_query("SELECT count(*) AS total FROM admin_forum Where parent_id=".$row->comment_id);
	    $result = mysql_fetch_array($query_n);
	    $tmp->child = $result['total'];
	    $tmp->root_id= $row->root;
	    $tmp->comment_by= $row->first_name;
	    $tmp->comment = $row->comment;
	    $tmp->date = $row->date;
	    $tmp->update_date= $row->update_date;
	    $tmp->priority= $row->priority;
		$tmp->ref_attach= $row->ref_attach;
		$tmp->attachment_array = self::getAllAttachmentByID($row->ref_attach);
	    return $tmp;
	}
	
	public function getAllAttachmentByID($ref)
	{
  		$query_data = "SELECT * FROM attachments WHERE reference_id='".$ref."'" ; 
        $result_data = mysql_query($query_data) or die("Query ERROR".$query_data);
        $arrayCollection = new ArrayCollection();
		$tmps = array();
		$i = 0;
		
        while ($row =  mysql_fetch_object($result_data)) 
		{
            $tmp = new AttachmentVO();
			$tmp->attach_id = $row->attach_id;
			$tmp->comment_id = $row->comment_id;
			$tmp->org_file_name = $row->org_file_name;
			$tmp->server_file_name = $row->server_file_name;
			$tmp->date = $row->date;
			$tmp->reference_id = $row->reference_id;
			
			$tmps[$i] =	$tmp;
			$i++;
		}
		if(count($tmps) >0)
			$arrayCollection->source = $tmps;
		return $arrayCollection;
	}

	public function addSubject($subject)
    {
        $parent_ID = 'NULL';
		$ref_id = uniqid();
        if($subject->parent_id != 0)
        {
            $parent_ID = $subject->parent_id;
			
        }  
        $myDate = date('Y-m-d H:i:s');
      
        if($subject->root_id ==0)
        {
            $result = mysql_query("SHOW TABLE STATUS LIKE 'admin_forum'");
            $row = mysql_fetch_array($result);
            $subject->root_id = $row['Auto_increment'];
        }
        else
        {
            $query_up = mysql_query("UPDATE admin_forum SET update_date ='".$myDate."' WHERE root=".$subject->root_id);
        }
		
        $my_insert = "INSERT INTO admin_forum ( `comment_id` , `parent_id` , `admin_id` , `root`, `comment` ,`date`, `update_date`, `priority`, `ref_attach`) VALUES (NULL,".$parent_ID.",".$subject->admin_id.",".$subject->root_id.",'".$subject->comment."','" .$myDate."','" .$myDate."','" .$subject->priority."','".$ref_id."')";
        $result_insert = mysql_query($my_insert) or die("Query ERROR".$my_insert);
		$lastInsertedID = mysql_insert_id();
		
		if($result_insert)
		{
			 for($i = 0; $i< count($subject->attachmentIDs); $i++)
			 {
			 	$update_attach = "UPDATE attachments SET reference_id = '".$ref_id."', comment_id = ".$lastInsertedID." 
			 	WHERE attach_id = ".$subject->attachmentIDs[$i] ;
				$result_update = mysql_query($update_attach) or die("Query ERROR".$update_attach);
			 }
		}
		
		if($subject->parent_id != 0)
		{
			$ad_query = mysql_query('SELECT first_name, email FROM administrator, (SELECT admin_id FROM admin_forum WHERE comment_id = '.$subject->parent_id.') a WHERE administrator.admin_id = a.admin_id');
			$ad_result = mysql_fetch_array($ad_query);
			
			$action_text = 'You have a reply from '.$ad_result['first_name'].'. Please Check your forum with SUB ID:'. $subject->root_id;
			$act_query = mysql_query("INSERT INTO notification (`admin_id`, `comment`, `date`) VALUES (".$subject->admin_id.",'".$action_text."','".$myDate."')");
			
			$_instance = new TestEmail();
			$_instance->sendEmail($subject->root_id, 'ForumReply', $ad_result['email'], 0, $ad_result['first_name']);
		}
		else
		{
			$_instance = new TestEmail();
			$_instance->sendEmail($subject->root_id, 'AddForum', 'gopinath@synergyquad.com', 0, 'Gopi');
				
		
		}
		$forum = new ForumVO();
        $forum->parent_id = $subject->parent_id;
        $forum->start_index = 0;
        $forum->interval = 10;
        $replyList = self::getCommentListByParentID($forum);
        
        return $replyList;
    }
    
	public function deleteSubject($commentID)
    {
        $my_delete = "DELETE FROM admin_forum WHERE comment_id=".$commentID;
        $result_insert = mysql_query($my_delete) or die("Query ERROR");
        return $result_insert;
    }
    
	public function getNotificationByUserID($userID) 
	{
		$not_insert = mysql_query("SELECT * FROM notification WHERE admin_id=".$userID) or die("Query ERROR");
		
		$arrayCollection = new ArrayCollection();
	    $tmps = array();
	    $i = 0;

	    while ($row =  mysql_fetch_object($not_insert))
	    {
	        $tmps[$i] =   self::createNotificationList($row);
	        $i++;
	    }
	    $arrayCollection->source = $tmps;
		return $arrayCollection;
    }
    
	private function createNotificationList($row)
	{   
	    $tmp = new NotificationVO();
	    $tmp->not_id = $row->not_id;
	    $tmp->admin_id = $row->admin_id;
	    $tmp->comment = $row->comment;
	    $tmp->date = $row->date;
	    return $tmp;
	}
    
	public function deleteNotification($notID) 
	{
		$note_delete = mysql_query("DELETE FROM notification WHERE not_id=".$notID) or die("Query ERROR");
        return $note_delete;
    }
} 
?>